/*
 * $RCSfile: OracleConnection.java,v $
 * $Revision: 1.26 $
 *
 * Comments:
 *
 * (C) Copyright Biuro-Technika 2004.  All rights reserved.
 * THIS IS UNPUBLISHED PROPRIETARY SOURCE CODE OF Biuro-Technika
 * The copyright notice above does not evidence any
 * actual or intended publication of such source code.
 *
 * $Author: michal $          $Locker:  $
 * $Date: 2010/06/13 07:42:30 $
 * $Log: OracleConnection.java,v $
 * Revision 1.26  2010/06/13 07:42:30  michal
 * optymalizacja petli
 *
 * Revision 1.25  2010/04/26 11:12:43  michal
 * dodanie pol current_timestamp oraz current_date
 *
 * Revision 1.24  2010/04/13 09:35:06  michal
 * parametryzacja kodu
 *
 * Revision 1.23  2010/02/25 10:34:15  mdettla
 * Poprawienie metody datetimeExtract.
 *
 * Revision 1.22  2010/02/24 14:32:39  mdettla
 * Dodanie metody datetimeExtract.
 *
 * Revision 1.21  2010/01/29 14:32:29  michal
 * uporzadkowanie kodu
 *
 * Revision 1.20  2009/10/20 08:53:12  michal
 * kompatybilnosc z postgresem
 *
 * Revision 1.19  2009/09/22 11:46:34  michal
 * ParaSoft -> Biuro-Technika
 *
 * Revision 1.18  2009/05/25 08:00:15  michal
 * ddoanie implementacji metody alterColumnType
 *
 * Revision 1.17  2009/05/15 07:09:28  michal
 * dodanie implementacji existFunction,existTrigger oraz nadpisanie getFullTableColumn
 *
 * Revision 1.16  2008/12/09 08:23:12  michal
 * dodanie metod dropColumn oraz sTempSelect
 *
 * Revision 1.15  2008/03/06 12:04:45  muzzy
 * organizacja importow
 *
 * Revision 1.14  2007/11/13 12:30:38  muzzy
 * nowa existsFunction
 *
 * Revision 1.13  2007/11/07 14:01:05  muzzy
 * poprawki w existsIndex
 *
 * Revision 1.12  2007/11/07 13:22:36  muzzy
 * poprawki w dodawaniu indexow
 *
 * Revision 1.11  2007/10/25 14:37:33  muzzy
 * poprawki w indexach, round i numericCast
 *
 * Revision 1.10  2007/09/20 15:25:31  dominik
 * Zmiana implementacji leftJoin
 *
 * Revision 1.9  2007/08/24 09:07:54  dominik
 * Implementacja dodatkowych metod
 *
 * Revision 1.8  2007/08/21 09:18:43  dominik
 * Dodanie metod createTableSpace i createScheam
 *
 * Revision 1.7  2007/01/16 15:50:45  dominik
 * Zamiana PreparedStatement na DebuggablePreparedStatement - debugowanie zapytania
 *
 * Revision 1.6  2006/01/31 15:39:12  muzzy
 * sprawdzanie istnienia funkcji
 *
 * Revision 1.5  2005/10/13 14:15:29  muzzy
 * triggery, funkcje i left join
 *
 * Revision 1.4  2005/01/18 15:01:03  limbur
 * Dodano funkcje bitand(...)
 *
 * Revision 1.3  2005/01/05 17:57:07  limbur
 * Uwzgledniono kolejne roznice pomiedzy Oracle - PostgreSQL
 *
 * Revision 1.2  2004/12/17 17:11:20  limbur
 * PostgreSQL - kolejne zmiany
 *
 * Revision 1.1  2004/12/15 08:41:07  limbur
 * Wersja pod PostgreSQL
 *
 */

package parasoft.centrap.connection;

import static parasoft.centrap.utils.Error.sql_index;
import static parasoft.centrap.utils.Error.sql_seq_currval;
import static parasoft.centrap.utils.Error.sql_seq_next;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import parasoft.centrap.migration.Column;
import parasoft.centrap.migration.Index;


public class OracleConnection extends DbConnection
{
    public OracleConnection(final DbConnectionBroker broker)
        throws SQLException
    {
        this(broker,false);
    }
    public OracleConnection(final DbConnectionBroker broker,final Connection conn)
    throws SQLException
	{
	    super(broker, conn);
	}
    public OracleConnection(final DbConnectionBroker broker,final boolean last)
    throws SQLException
	{
	    super(broker,last);
	}

    @Override
	public boolean existsSchema(final String schema) throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement(
                "SELECT USER_ID FROM ALL_USERS WHERE UPPER(USERNAME)=?" );
            stmt.setString(1, correct(schema));
            rs = stmt.executeQuery();
            return rs.next();
        } finally {
            closeStatement(rs, stmt);
        }
    }

    @Override
	public void dropTable(final String table) throws SQLException
    {
        executeUpdate("DROP TABLE " + table + " CASCADE CONSTRAINTS");
    }

    @Override
	public boolean existsTable(final String table) throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT TABLE_NAME" +
                                    " FROM USER_ALL_TABLES" +
                                    " WHERE TABLE_NAME=?");
            stmt.setString(1, correct(table));
            rs = stmt.executeQuery();
            return rs.next();
        } finally {
            closeStatement(rs, stmt);
        }
    }

    @Override
	public boolean existsTable(final String schema, final String table) throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT TABLE_NAME" +
                                    " FROM ALL_TABLES" +
                                    " WHERE OWNER=? AND TABLE_NAME=?");
            stmt.setString(1, correct(schema));
            stmt.setString(2, correct(table));
            rs = stmt.executeQuery();
            return rs.next();
        } finally {
            closeStatement(rs, stmt);
        }
    }

    @Override
	public List<Column> getFullTableColumns(final String schema, final String table)
    		throws SQLException {
    	return super.getFullTableColumns(schema, table);
    }

    @Override
	public List<String> getTableColumns(final String table) throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
        	final List<String> columns = new ArrayList<String>();
            stmt = prepareStatement("SELECT COLUMN_NAME" +
                                    " FROM USER_TAB_COLUMNS" +
                                    " WHERE TABLE_NAME=?");
            stmt.setString(1, correct(table));
            rs = stmt.executeQuery();
            while(rs.next()) {
                columns.add(rs.getString(1));
            }
            return columns;
        } finally {
            closeStatement(rs, stmt);
        }
    }

    @Override
	public List<String> getTableColumns(final String schema, final String table) throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
        	final List<String> columns = new ArrayList<String>();
            stmt = prepareStatement("SELECT COLUMN_NAME" +
                                    " FROM ALL_TAB_COLUMNS" +
                                    " WHERE OWNER=? AND TABLE_NAME=?");
            stmt.setString(1, correct(schema));
            stmt.setString(2, correct(table));
            rs = stmt.executeQuery();
            while(rs.next()) {
                columns.add(rs.getString(1));
            }
            return columns;
        } finally {
            closeStatement(rs, stmt);
        }
    }

    @Override
	public boolean existsTableColumn(final String table, final String column)
        throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT COLUMN_NAME" +
                                       " FROM USER_TAB_COLUMNS" +
                                       " WHERE TABLE_NAME=? AND COLUMN_NAME=?");
            stmt.setString(1, correct(table));
            stmt.setString(2, correct(column));
            rs = stmt.executeQuery();
            return rs.next();
        } finally {
            closeStatement(rs, stmt);
        }

    }

    @Override
	public boolean existsTableColumn(final String schema, final String table, final String column)
        throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT COLUMN_NAME" +
                                        " FROM ALL_TAB_COLUMNS" +
                                        " WHERE OWNER=? AND TABLE_NAME=? AND" +
                                            " COLUMN_NAME=?");
            stmt.setString(1, correct(schema));
            stmt.setString(2, correct(table));
            stmt.setString(3, correct(column));
            rs = stmt.executeQuery();
            return rs.next();
        } finally {
            closeStatement(rs, stmt);
        }

    }

    @Override
	public void createSequence(final String key) throws SQLException
    {
        executeUpdate("CREATE SEQUENCE " + key +
                      " START WITH 1 INCREMENT BY 1" +
                      " MINVALUE 1 NOMAXVALUE NOCYCLE ORDER");
    }

    @Override
	public void createSequence(final String key, final long start) throws SQLException
    {
        executeUpdate("CREATE SEQUENCE " + key +
                      " START WITH " + start + " INCREMENT BY 1" +
                      " MINVALUE 1 NOMAXVALUE NOCYCLE ORDER");
    }

    @Override
	public void dropSequence(final String key) throws SQLException
    {
        executeUpdate("DROP SEQUENCE " + key);
    }

    @Override
	public boolean existsSequence(final String key) throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT SEQUENCE_NAME" +
                                    " FROM USER_SEQUENCES" +
                                    " WHERE SEQUENCE_NAME=?");
            stmt.setString(1, correct(key));
            rs = stmt.executeQuery();
            return rs.next();
        } finally {
            closeStatement(rs, stmt);
        }
    }

    @Override
	public boolean existsSequence(final String schema, final String key) throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT SEQUENCE_NAME" +
                                    " FROM ALL_SEQUENCES" +
                                    " WHERE" +
                                    " SEQUENCE_OWNER=? AND SEQUENCE_NAME=?");
            stmt.setString(1, correct(schema));
            stmt.setString(2, correct(key));
            rs = stmt.executeQuery();
            return rs.next();
        } finally {
            closeStatement(rs, stmt);
        }
    }

    @Override
	public String sCurrVal(final String key)
    {
        return key + ".CURRVAL";
    }

    @Override
	public String sNextVal(final String key)
    {
        return key + ".NEXTVAL";
    }

    @Override
	public long currentId(final String key) throws SQLException
    {
        ResultSet rs = null;
        try {
            rs = executeQuery("SELECT " + key + ".CURRVAL FROM DUAL");
            if(rs.next()) {
                return rs.getLong(1);
            }
            else {
                throw sql_seq_currval.sql(key);
            }
        } finally {
            closeStatement(rs);
        }
    }

    @Override
	public long nextId(final String key) throws SQLException
    {
        ResultSet rs = null;
        try {
            rs = executeQuery("SELECT " + key + ".NEXTVAL FROM DUAL");
            if(rs.next()) {
                return rs.getLong(1);
            }
            else {
                throw sql_seq_next.sql(key);
            }
        } finally {
            closeStatement(rs);
        }
    }
    @Override
	public String createIndexName(final String schema, final String table, final String column)
    {
        final String indexname = schema + "_" + table + "_" + column;
        return indexname.length() > 30 ? indexname.substring(0, 30) : indexname;
    }

    @Override
	public void createIndex(final String schema, final String table, final String column) throws SQLException
    {
        try {
            executeUpdate("CREATE INDEX " + createIndexName(schema, table, column) +
                    " ON " + table + "(" + column + ")");
        } catch (final SQLException e) {
            throw sql_index.sql(e.getMessage(), createIndexName(schema, table, column));
        }
    }

    @Override
	public void createIndex(final String index_name, final String table, final List<String> columns)
        throws SQLException
    {
        String query = "CREATE INDEX " + index_name +
            " ON " + table + "(" + columns.get(0);
        for(int i = 1, len = columns.size(); i < len; i++) {
            query += "," + columns.get(i);
        }
        query += ")";
        try {
            executeUpdate(query);
        } catch (final SQLException e) {
            throw sql_index.sql(e.getMessage(), query);
        }
    }

    @Override
	public void dropIndex(final String schema, final String table, final String column) throws SQLException
    {
        executeUpdate("DROP INDEX " + createIndexName(schema, table, column));
    }

    @Override
	public void dropIndex(final String index_name) throws SQLException
    {
        executeUpdate("DROP INDEX " + index_name);
    }

    @Override
	public boolean existsIndex(final String table, final String column) throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
		final String schema = "";
        try {
            stmt = prepareStatement("SELECT * FROM USER_INDEXES WHERE " +
                                    "OWNER=? AND TABLE_NAME=? AND " +
                                    "INDEX_NAME=?");
            stmt.setString(1, correct(schema));
            stmt.setString(2, correct(table));
            stmt.setString(3, correct(table) + "_" + correct(column));
            rs = stmt.executeQuery();
            return rs.next();
        } finally {
            closeStatement(rs, stmt);
        }
    }

    @Override
	public boolean existsIndex(final String schema, final String table, final String column)
        throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT * FROM ALL_INDEXES WHERE " +
                                    "TABLE_OWNER=? AND TABLE_NAME=? AND " +
                                    "INDEX_NAME=?");
            stmt.setString(1, correct(schema));
            stmt.setString(2, correct(table));
            stmt.setString(3, createIndexName(correct(schema),
                    correct(table), correct(column)));
            rs = stmt.executeQuery();
            if (rs.next()) {
                return true;
            }
            closeStatement(rs, stmt);
            final String indexname = correct(table) + "_" + correct(column);
            stmt = prepareStatement("SELECT * FROM ALL_INDEXES WHERE " +
                    "TABLE_OWNER=? AND TABLE_NAME=? AND " +
                    "INDEX_NAME=?");
            stmt.setString(1, correct(schema));
            stmt.setString(2, correct(table));
            stmt.setString(3, indexname);
            rs = stmt.executeQuery();
            if (rs.next()) {
                dropIndex(indexname);
            }
        } finally {
            closeStatement(rs, stmt);
        }
        return false;
    }

    @Override
	public boolean existsIndex(final String index_name) throws SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT TABLE_NAME FROM ALL_INDEXES WHERE INDEX_NAME=?");
            stmt.setString(1, correct(index_name));
            rs = stmt.executeQuery();
            return rs.next();
        } finally {
            closeStatement(rs, stmt);
        }
    }

    /**
     * <li>Dodaje do zapytania <i>"ROWNUM=1"</i>.
     * <li>Sprawdza wyst�pienie klauzuli <i>"WHERE"</i> oraz dodaje w przypadku braku
     * @param query
     * @see parasoft.centrap.connection.DbConnection#appendQueryLimit(java.lang.String)
     */
    @Override
	public String appendQueryLimit(final String query)
    {
        if((query.indexOf("WHERE") > 0)) {
            return query + " AND ROWNUM=1";
        } else {
            return query + " WHERE ROWNUM=1";
        }
    }

    @Override
	public String decodeQuery(final String expression, final List<String> values, final List<String> results)
    {
        String query = "DECODE(" + expression;
        int i = 0;
        while(i < values.size()) {
            query += "," + values.get(i) + "," + results.get(i);
            i++;
        }
        query += "," + results.get(i) + ")";

        return query;
    }

    @Override
	public String decodeQuery(final String expression, final List<String> values, final List<String> results, final boolean resround)
    {
        return decodeQuery(expression, values, results);
    }

    @Override
	public String dateTrunc(final String column)
    {
        return "TRUNC(" + column + ")";
    }
    
    @Override
	public String currentTimestamp(){
    	return "SYSTIMESTAMP";
    }
    @Override
	public String currentDate(){
    	return "SYSDATE";
    }

    @Override
	public String datetimeExtract(final String datetimePart, final String column) {
    	String extracted = "EXTRACT(" + datetimePart + " FROM " + column + ")";
    	if ("hour".equalsIgnoreCase(datetimePart)) {
    		extracted += " + 1";
    	}
    	return extracted;
    }

    @Override
	public String secOfDay(final String column)
    {
        return "CAST(TO_CHAR(" + column + ", 'SSSSS') AS NUMBER)";
    }

    @Override
	public String numericCast(final String column)
    {
        return "CAST(" + column + " AS NUMBER)";
    }

    @Override
	public String varcharCast(final String column)
    {
        return column;
    }

    @Override
	public String varcharCast()
    {
        return "(?)";
    }

    @Override
	public String power(final String number, final String exponent)
    {
        return "POWER(" + number + "," + exponent + ")";
    }

    @Override
	public String round(final String number, final int precision)
    {
        return "ROUND(" + number + "," + precision + ")";
    }

    @Override
	public String bitand(final String arg1, final String arg2)
    {
        return "BITAND(" + arg1 + "," +  arg2 + ")";
    }

    @Override
	public String concat(final String str1, final String str2)
    {
        return "CONCAT(" + str1 + "," +  str2 + ")";
    }

    @Override
	public String sTempSelect(final String column)
    {
    	return "select ("+column+") from dual";
    }

    @Override
	public boolean isOuterJoin()
    {
        return false;
    }

    @Override
	public String leftJoin(final String column, final String condition)
    {
        return " LEFT OUTER JOIN " + column + " ON (" + condition  + ")" ;
    }

    private void createTableSpace(final String name) throws SQLException
    {
    	final String query = "CREATE TABLESPACE " + name  +
    			" DATAFILE '"+name+".dat' SIZE 50 M REUSE" +
    			" AUTOEXTEND ON NEXT 10 M MAXSIZE UNLIMITED "+
    			" DEFAULT STORAGE ( "+
    				" INITIAL 1 M"+
    				" NEXT 1 M"+
    				" MINEXTENTS 1"+
    				" MAXEXTENTS UNLIMITED"+
    				" PCTINCREASE 0" +
    			" )";
        executeUpdate(query);
    }

    @Override
	public void createSchema(final String schema) throws SQLException
    {
    	createTableSpace(schema);
    	String query = "CREATE USER "+schema + " IDENTIFIED BY replicate DEFAULT TABLESPACE "+ schema;
        executeUpdate(query);

    	query = "GRANT CREATE SESSION TO "+ schema;
        executeUpdate(query);

    	query = "GRANT DBA TO " + schema;
        executeUpdate(query);
    }

    //TODO NOT IMPLEMENTED YET!!!
    @Override
	public void createFunction(final String name, final String functBody) throws java.sql.SQLException
    {
    }
    //TODO NOT IMPLEMENTED YET!!!
    @Override
	public void dropFunction(final String name) throws java.sql.SQLException
    {
    }

    @Override
	public boolean existsFunction(final String name) throws java.sql.SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE=?" +
                                    "OBJECT_NAME=?");
            stmt.setString(1, "FUNCTION");
            stmt.setString(2, correct(name));
            rs = stmt.executeQuery();
            return rs.next();
        } finally {
            closeStatement(rs, stmt);
        }
    }

    @Override
	public boolean existsFunction(final String name, final String functbody) throws java.sql.SQLException
    {
        return true;//TODO ZMIENIC
    }

    @Override
	public void createTrigger(final String name, final String tableName, final String functName) throws java.sql.
        SQLException
    {//TODO NOT IMPLEMENTED YET!!!
    }

    public void dropTrigger(final String name) throws java.sql.SQLException
    {//TODO NOT IMPLEMENTED YET!!!
    }

    @Override
	public boolean existsTrigger(final String triggerName, final String schema, final String tableName) throws java.sql.SQLException
    {
        DebuggablePreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = prepareStatement("SELECT * FROM USER_TRIGGERS WHERE " +
                                    "TABLE_OWNER=? AND TRIGGER_NAME=? AND " +
                                    "TABLE_NAME=?");
            stmt.setString(1, correct(schema));
            stmt.setString(2, correct(triggerName));
            stmt.setString(3, correct(tableName));
            rs = stmt.executeQuery();
            return rs.next();
        } finally {
            closeStatement(rs, stmt);
        }
    }

    /***
     *
     * @return liste schem (user'ow) z wyjatkiem
     * zawierajacych fragment nazwy SYS i $ - schemy systemowe
     * @throws SQLException
     */
    @Override
	public List<String> getSchemas() throws SQLException
    {
        final DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet rs = null;
        final List<String> lista = new ArrayList<String>();
        try {
            rs = dmd.getSchemas();
            while(rs.next()) {
            	if((rs.getString(1).indexOf("SYS")==-1) && (rs.getString(1).indexOf("$")==-1)) {
//                	System.out.println(rs.getString(1));
            		lista.add(rs.getString(1));
            	}
            }
            return lista;
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

    @Override
	public List<Index> getIndexs(final String schema, final String table) throws java.sql.SQLException
    {
        final DatabaseMetaData dmd = getConnection().getMetaData();
        ResultSet rs = null;
        final List<Index> list = new ArrayList<Index>();
        final Map<String, Index> map = new HashMap<String, Index>();
        List<String> columns = null;
        try {
            rs = dmd.getIndexInfo(null,
                                  schema, table,
                                  false, false);
            while(rs.next()) {
            	if(rs.getString(9) != null) {
            		final String column = rs.getString(9);
            		boolean unique = true;
            		if(rs.getString(4).equals("1")) {
            			unique = false;
            		}
             		final String indexName = rs.getString(6);
            		if(indexName.indexOf("SYS")==-1) {
            			System.out.println(schema +" "+table +" "+column +" "+unique +" "+indexName);

            			if(map.containsKey(indexName)) {
            				final Index index = map.get(indexName);
            				final List<String> indexColumns = index.getColumns();
            				indexColumns.add(column);
            			}
            			else {
            				columns = new ArrayList<String>();
            				columns.add(column);
            				map.put(indexName, new Index(schema, table, columns, unique, indexName));
            			}


//            			list.add(new Index(schema, table, column, unique, indexName));
            		}
            	}
            }
            list.addAll(map.values());
            return list;
        } finally {
            if(rs != null) {
                rs.close();
            }
        }
    }

	@Override
	public void createIndex(final String index_name, final String table, final List<String> columns,
			final boolean unique) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void alterColumnType(final String tableName, final String columnName, final String newType) throws java.sql.SQLException{
		executeUpdate( "ALTER TABLE " + tableName + " MODIFY " +
				columnName + " " + newType );
	}

	public List<JdbsType> getStandardTypes() {
		return null;
	}
    /**
     * @param param
     * @return String
     */ 
    @Override
	public String correct(final String param){
    	final String out = param;
    	if(param!=null){
	    	return out.toUpperCase();
    	}
    	return out;
    }    
}
